create table Person
(
	embg varchar(20) not null,
	first_name nvarchar(30) not null,
	last_name nvarchar(50) not null,
	username nvarchar(30) not null,
	password nvarchar(30) not null,
	address nvarchar(50),
	city nvarchar(30),
	birth_date datetime,
	birth_place nvarchar(30),
	primary key (embg)
)


create table Department
(
	department_id bigint not null,
	description nvarchar(70) not null,
	visit_start datetime null,
	visit_end datetime null,
	primary key (department_id)
)

--drop table Personnel
create table Personnel
(
	embg varchar(20) not null,
	department_id bigint null,
	entry_date datetime null,
	spec_type_id int null,
	foreign key (embg) references Person(embg) on delete cascade,
	foreign key (department_id) references Department(department_id) on delete set null,
	foreign key (spec_type_id) references Spec_Type(spec_type_id) on delete set null
)

create table Spec_Type
(
	spec_type_id int not null,
	description nvarchar(100) not null,
	primary key (spec_type_id)
)

create table Room
(
	room_id bigint not null,
	department_id bigint null,
	number nvarchar(10) null,
	status bit not null,
	info nvarchar(max) null,
	primary key (room_id),
	foreign key (department_id) references Department(department_id) on delete set null
)

--drop table Examination
create table Examination
(
	examination_id bigint not null,
	personnel_embg varchar(20) not null,
	person_embg varchar(20) not null,
	start_date datetime null,
	end_date datetime null,
	examination_type_id int null,
	primary key (examination_id),
	foreign key (personnel_embg) references Person(embg) on delete no action,
	foreign key (person_embg) references Person(embg) on delete no action,
	foreign key (examination_type_id) references Examination_Type on delete set null,
	check (personnel_embg <> person_embg)
)

create table Examination_Type
(
	examination_type_id int not null,
	description varchar(50) not null,
	primary key (examination_type_id),
)

create table Bed
(
	bed_id bigint not null,
	room_id bigint null,
	number nvarchar(10) not null,
	status bit not null,
	primary key (bed_id),
	foreign key (room_id) references Room(room_id) on delete set null
)

--drop table Bed_History
create table Bed_History
(
	history_id bigint not null,
	examination_id bigint not null default -1,
	bed_id bigint not null default -1,
	end_date datetime null,
	info nvarchar(max),
	primary key (history_id),
	foreign key (examination_id) references Examination(examination_id) on delete set default,
	foreign key (bed_id) references Bed(bed_id) on delete set default
)

create table Note
(
	note_id bigint not null,
	note_type_id int null,
	examination_id bigint not null,
	description nvarchar(max) null,
	date datetime null,
	primary key (note_id),
	foreign key (note_type_id) references Note_Type(note_type_id) on delete set null,
	foreign key (examination_id) references Examination(examination_id) on delete cascade
)


create table Note_Type
(
	note_type_id int not null,
	description varchar(50) not null,
	primary key (note_type_id),
)

create table Roles
(
	role_id bigint not null,
	name nvarchar(30) not null,
	description nvarchar(50) null ,
	primary key (role_id)
)

create table Forms
(
	form_id bigint not null,
	name nvarchar(30) not null,
	description nvarchar(50) null ,
	primary key (form_id)
)

create table FormActions
(
	action_id bigint not null,
	form_id bigint not null,
	name nvarchar(30) not null,
	description nvarchar(50) null ,
	primary key (action_id),
	foreign key (form_id) references Forms(form_id) on delete cascade	
)

create table Policies
(
	embg varchar(20) not null,
	role_id bigint not null default -1,
	form_id bigint not null default -1,
	foreign key (embg) references Person(embg) on delete cascade,
	foreign key (role_id) references Roles(role_id) on delete set default,
	foreign key (form_id) references Forms(form_id) on delete set default
)

create table RoleDefinitions
(
	role_id bigint not null,
	action_id bigint not null default -1,
	foreign key (role_id) references Roles(role_id) on delete cascade,
	foreign key (action_id) references FormActions(action_id) on delete set default
)













	


	 
	